package cn.ainit.bj.bjxt;

import cn.ainit.sys.common.dao.BusinessBaseDao;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

@Transactional
@Repository("bjTopicsDao")
public class BJTopicsDaoImpl extends BusinessBaseDao implements BJTopicsDao {
	
	@Override
	public boolean updateObject(String sqlText, String[] valuesArr,
			String[] whereValue) throws Exception {
		return this.executeUpdateSql(sqlText, valuesArr, whereValue);
	}
	@Override
	public BJTopics getOneBjTopic(String dsId) throws Exception {
		return (BJTopics) this.sessionFactory.getCurrentSession().get(BJTopics.class, dsId);
	}
	
	@Override
	public boolean saveTopics(BJTopics bjTopics) throws Exception {
		this.getSessionFactory().getCurrentSession().save(bjTopics);
		return true;
	}
	
	@Override
	public boolean updateTopics(BJTopics bjTopics) throws Exception {
		this.getSessionFactory().getCurrentSession().update(bjTopics);
		return true;
	}
	
	@Override
	public String makeLiXiang(String xtId) throws Exception {
		String putValue="";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			conn.setAutoCommit(false);
			 String sql = "{Call PK_BJ_XT.up_ChToLx(?,?)}";
			 cs = conn.prepareCall(sql);
			 cs.setString(1,xtId);
			 cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
			 cs.execute();
			 putValue = cs.getString(2);
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}finally{
			if(cs != null){
				cs.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
		return putValue;
	}
	
	@Override
	public boolean piPeiBookList(String xtId,String hId) throws Exception {
		boolean flag=false;
		Connection conn=null;
		CallableStatement cs=null;
		try {
			 conn= this.getJdbcTemplate().getDataSource().getConnection();
			 String sql = "{Call PK_BJ_XT.up_SetXtHid(?,?)}";
			 cs = conn.prepareCall(sql);
			 cs.setString(1,xtId);
			 cs.setString(2,hId);
			 cs.execute();
			 flag=true;
		} catch (Exception e) {
			e.getMessage();
			throw e;
		}finally{
			if(cs!=null){
				cs.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		return flag;
	}
	
	@Override
	public List<String> getColsByDsId(String dsId) {
		List<String> list = new ArrayList<String>();
		try {
			try {
				String hql = "from sysCloumns s where s.dsId =? ";
				Query query = sessionFactory.getCurrentSession().createQuery(hql);
				query.setString(0, dsId);
				list = query.list();
			} catch (Exception e) {
				e.printStackTrace();
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	@Override
	public String getUpdateTable(String colName) {
		String table=null;
		try {
			String sql="select update_table from sys_columns sc where sc.java_property_recorded='"+colName+"'";
			table=this.jdbcTemplate.queryForObject(sql,String.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return table;
	}

	@Override
	public String isbnCIP(String xtId,String manId) throws Exception {
		String putValue="";
		final String fxtId = xtId;
		Connection conn= null;
		CallableStatement cs = null;
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			conn.setAutoCommit(false);
			 String sql = "{Call PK_BJ_XT.up_LxXTToISBN(?,?,?)}";
			 cs = conn.prepareCall(sql);
			 cs.setString(1,xtId);
			 cs.setString(2,manId);
			 cs.registerOutParameter(3,oracle.jdbc.OracleTypes.VARCHAR);
			 cs.execute();
			 putValue = cs.getString(3);
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}finally{
			if(cs != null){
				cs.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
		return putValue;
	}
	@Override
	public String sanShen(String xtId,String manId) throws Exception {
		String putValue="";
		final String fxtId = xtId;
		Connection conn= null;
		CallableStatement cs = null;
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			conn.setAutoCommit(false);
			 String sql = "{Call PK_BJ_XT.up_LxXTToShenHe(?,?,?)}";
			 cs = conn.prepareCall(sql);
			 cs.setString(1,xtId);
			 cs.setString(2,manId);
			 cs.registerOutParameter(3,oracle.jdbc.OracleTypes.VARCHAR);
			 cs.execute();
			 putValue = cs.getString(3);
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}finally{
			if(cs != null){
				cs.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
		return putValue;
	}
	@Override
	public String beginPlan(String xtId, String manId) throws Exception {
		String putValue="";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			 conn= this.getJdbcTemplate().getDataSource().getConnection();
			 String sql = "{Call PK_BJ_XT.up_GetXtManage(?,?)}";
			 cs = conn.prepareCall(sql);
			 cs.setString(1,xtId);
			 cs.setString(2,manId);
			 cs.execute();
			 putValue = "success";
		} catch (Exception e) {
			e.printStackTrace();
			putValue ="fail";
			throw e;
		}finally{
			if(cs != null){
				cs.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
		return putValue;
	}
	
	@Override
	public boolean execSql(String sql) throws Exception {
		return this.executeSQL(sql);
	}
	
//	@Override
//	public boolean  judgeXtId(String xtId) throws Exception {
//		try {
//			String hql="from BJTopics  where djType='LX' and xtId='"+xtId+"'";
//			Query query = sessionFactory.getCurrentSession().createQuery(hql);
//			List<BJTopics> list=query.list();
//			if(list!=null&&list.size()!=0&&(BJTopics)list.get(0)!=null){
//				BJTopics bjTopics=(BJTopics)list.get(0);
//				if(!bjTopics.getXtId().equals("")){
//					return true;
//				}else{
//					return false;
//				}
//			}else{
//				return false;
//			}
//			
//		} catch (Exception e) {
//			e.printStackTrace();
//			return false;
//		}
//	
//	}
	@Override
	public String xtPlanRight(String xtId, String manId) throws Exception {
		String str = "";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			 conn= this.getJdbcTemplate().getDataSource().getConnection();
			 String sql = "{Call  PK_BJ_XT.up_xtPlanRight(?,?,?)}";
			 cs = conn.prepareCall(sql);
			 cs.setString(1,xtId);
			 cs.setString(2,manId);
			 cs.registerOutParameter(3,oracle.jdbc.OracleTypes.VARCHAR);
			 cs.execute();
			 str = cs.getString(3);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(cs!=null){
				cs.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		return str;
	}
	@Override
	public List getzhanghaobymanid(String mb) {
		List list=new ArrayList();
		try {
			String sql="select * from ( select m.*, b.man_id INTERPRETER_MAN, b.man_id JIAFANG, b.man_id MAN_BUSINESS from MULTI_BANK_INFO m left join BS_MAN b on m.sin_id = b.card_no and b.MAN_TYPE <> 'owner' union all select m.*,'' INTERPRETER_MAN,'' JIAFANG, bc.clients_id MAN_BUSINESS from MULTI_BANK_INFO m,bs_clients bc where m.sin_id = bc.card_no union all select mb.*,'' INTERPRETER_MAN,'' JIAFANG,op.o_id MAN_YUANGONG from MULTI_BANK_INFO mb,bs_operators op where mb.sin_id = op.o_card) temp where temp.man_business='"+mb+"'";
			list=this.jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	
	@Override
	public List getManInfoByManID(String mb) {
		List list=new ArrayList();
		try {
			String sql="SELECT * FROM VV_BJ_XTHTLQHY temp where temp.man_business = '"+mb+"'";
			list=this.jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

    @Override
    public List getInfobyParty1(String XT_NAME,String PAY_PARTY) {
        List list=new ArrayList();
        try {
            String sql="SELECT * FROM vv_bj_authorRec temp where temp.TOPIC_SELECTED= '"+XT_NAME+"' and  temp.PAY_PARTY='"+PAY_PARTY+"'";
            list=this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

	@Override
	public boolean checkExistsNotVerifyDj(String XT_NAME,String PAY_PARTY) {
		try {
			String sql="SELECT DISTINCT 1 FROM zc_authorcheck cd WHERE IS_DESTROY = 0 AND IS_VERIFY = 0 AND PAY_PARTY = '"+PAY_PARTY+"' AND TOPIC_SELECTED = '"+XT_NAME+"'";
			Long l =this.jdbcTemplate.queryForLong(sql);
			return null!=l?true:false;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}



    @Override
	public List getManAccounts(String man) {
		List list=new ArrayList();
		try {
			String sql="select mb.*,op.o_id,op.o_name from MULTI_BANK_INFO mb left join bs_operators op on mb.sin_id=op.o_card where op.o_id='"+man+"'";
			list=this.jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	@Override
	public List getXtYinCi(String xtId,String yinCi) {
		List list=new ArrayList();
		String yc=yinCi.substring(2);
		try {
			String sql="SELECT YS.Total_Amonut FROM YZ_SPRK YS left join yz_print_explain pe on ys.pe_id = pe.pe_id where YS.is_destroy <> '1' and ys.is_verify = '1' and ys.xt_id='"+xtId+"' and pe.yinci='"+yc+"'";
			list=this.jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	@Override
	public List getBSMoney(String xtId,String manID,String contract_TYPE,String type) {
		List list=new ArrayList();
		try {
			String sql="SELECT CT.DJ_TYPE, CT.CONTRACT_TYPE, pk_sys_ui.uf_parse_contract(CT.CONTRACT_TAX, CT.DJ_TYPE, 'ds_bj_contract2') CONTRACT_TAX,CT.CONTRACT_TAX SIN_ID,CT.XT_ID, CT.JIAFANG, CT.DJ_TYPE, BIC.H_PRICE, CT.DJ_CODE FROM BJ_CONTRACT CT LEFT JOIN BJ_ISBN_CIP BIC ON CT.XT_ID = BIC.XT_ID where CT.IS_VERIFY in ('1') AND CT.IS_DESTROY in ('0') AND CT.IS_EXIST in ('1') AND CT.XT_ID=(select sp.xt_id from yz_sprk sp where sp.sprk_id='"+xtId+"') AND JIAFANG='"+manID+"' AND CONTRACT_TYPE='"+contract_TYPE+"' AND (select name from bs_comparison  where comparison_id = 'contract' AND codeid=CT.DJ_TYPE)=(select name from bs_comparison  where comparison_id = 'contract' AND codeid='"+type+"')";
			list=this.jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
    @Override
         public List getZZBSMoney(String xtId,String manID) {
        List list=new ArrayList();
        try {
            String sql="select b.jiafang PAY_PARTY, B.XT_ID TOPIC_SELECTED,B.CONTRACT_TAX SIN_ID, pk_sys_ui.uf_parse_contract(b.contract_tax,b.dj_type,'ds_bj_contract2') PARAMETER_ROYALTY FROM bj_contract B left join BJ_ISBN_CIP bic on bic.xt_id = B.xt_id where b.jiafang='"+manID+"' and b.xt_id='"+xtId+"' and b.is_exist='1'";
            list=this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


//    @Override
//    public List getZZJTBSMoney(String sinid) {
//        List list=new ArrayList();
//        try {
//            String sql="SELECT T.*, ROWNUM XH FROM (SELECT MC.* FROM MULTI_CONTRACT_INFO MC WHERE MC.SIN_ID = '"+sinid+"' ORDER BY MC.END_AMOUNT ASC) T";
//            list=this.jdbcTemplate.queryForList(sql);
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        return list;
//    }

    @Override
    public List getamount(String PAY_PARTY, String BASIS_ACCOUNT, String TOPIC_SELECTED) {
        List list=new ArrayList();
        try {
            String sql="select NVL(sum(z.YNUMBER),0) AS YNUMBER, NVL(sum(z.JNUMBER),0) AS JNUMBER from  VV_ZZ_AMOUNT z where z.pay_party='"+PAY_PARTY+"'and Z.BASIS_ACCOUNT='"+BASIS_ACCOUNT+"'and Z.topic_selected='"+TOPIC_SELECTED+"'";
            list=this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
	@Override
	public List getDJtypeByc(String xtId,String manID,String contract_TYPE) {
		List list=new ArrayList();
		try {
			String sql="SELECT (select codeid from bs_comparison where comparison_id = 'contract' and codeid in ('contract.004', 'contract.005', 'contract.006', 'contract.007') and name = (select name from bs_comparison where comparison_id = 'contract' and codeid = CT.DJ_TYPE)) DJ_TYPE, (select name from bs_comparison where comparison_id = 'contract' and codeid = CT.DJ_TYPE) DJ_TYPE_NAME, CT.CONTRACT_TYPE, pk_sys_ui.uf_parse_contract(CT.CONTRACT_TAX, CT.DJ_TYPE, 'ds_bj_contract2') CONTRACT_TAX, CT.CONTRACT_TAX SIN_ID, CT.XT_ID, CT.JIAFANG, BIC.H_PRICE, CT.DJ_CODE FROM BJ_CONTRACT CT LEFT JOIN BJ_ISBN_CIP BIC ON CT.XT_ID = BIC.XT_ID where CT.IS_VERIFY in ('1') AND CT.IS_DESTROY in ('0') AND CT.IS_EXIST in ('1') AND CT.XT_ID = (select sp.xt_id from yz_sprk sp where sp.sprk_id = '"+xtId+"') AND JIAFANG = '"+manID+"' AND CONTRACT_TYPE = '"+contract_TYPE+"'";
			list=this.jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

    @Override
    public List getDJtypeByz(String xtId,String manID,String TYPE_TICKET) {
        List list=new ArrayList();
        try {
            String sql="SELECT (select codeid from bs_comparison where comparison_id = 'contract' and codeid in ('contract.001', 'contract.002', 'contract.003', 'contract.008') and name = (select name from bs_comparison where comparison_id = 'contract' and codeid = CT.DJ_TYPE)) DJ_TYPE, (select name from bs_comparison where comparison_id = 'contract' and codeid = CT.DJ_TYPE) DJ_TYPE_NAME,ct.BILL_TYPE, CT.CONTRACT_TYPE, pk_sys_ui.uf_parse_contract(CT.CONTRACT_TAX, CT.DJ_TYPE, 'ds_bj_contract2') CONTRACT_TAX, CT.CONTRACT_TAX SIN_ID, CT.XT_ID, CT.JIAFANG, BIC.H_PRICE, CT.DJ_CODE FROM BJ_CONTRACT CT LEFT JOIN BJ_ISBN_CIP BIC ON CT.XT_ID = BIC.XT_ID where CT.IS_VERIFY in ('1') AND CT.IS_DESTROY in ('0') AND CT.IS_EXIST in ('1') AND CT.XT_ID = (select sp.xt_id from yz_sprk sp where sp.sprk_id = '"+xtId+"') AND  ct.JIAFANG = '"+manID+"' AND ct.BILL_TYPE = '"+TYPE_TICKET+"'";
            list=this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
	
	@Override
	public List<String> getJTBM(List<String> runIds) throws Exception {
		Connection conn = null;
		CallableStatement cs = null;
		String returnStr = "";
		List<String> flagStr = new ArrayList<String>();
		try {
			conn =  this.getJdbcTemplate().getDataSource().getConnection();
			String sql = "{Call PK_CW_SAVE1.UF_AMOUNT_AGIN(?,?,?,?,?,?,?)}";
			cs = conn.prepareCall(sql);
			cs.setString(1,runIds.get(0));
			cs.setString(2,runIds.get(1));
			cs.setString(3,runIds.get(2));
			cs.setString(4,runIds.get(3));
			cs.setString(5,runIds.get(4));
			cs.setString(6,runIds.get(5));
			cs.registerOutParameter(7,oracle.jdbc.OracleTypes.VARCHAR);
			cs.execute();
			returnStr = cs.getString(7);
			flagStr.add(returnStr);
			cs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(cs != null){
				cs.close();
			}
			if(conn != null){
				conn.close();
			}
		}
		return flagStr;
	}
	
	
	@Override
	public List<String> getJTBMTWO(List<String> runIds) throws Exception {
		Connection conn = null;
		CallableStatement cs = null;
		String returnStr = "";
		List<String> flagStr = new ArrayList<String>();
		try {
			conn =  this.getJdbcTemplate().getDataSource().getConnection();
			String sql = "{Call PK_CW_SAVE1.UF_AMOUNT_AGINTWO(?,?,?,?,?,?,?,?)}";
			cs = conn.prepareCall(sql);
			cs.setString(1,runIds.get(0));
			cs.setString(2,runIds.get(1));
			cs.setString(3,runIds.get(2));
			cs.setString(4,runIds.get(3));
			cs.setString(5,runIds.get(4));
			cs.setString(6,runIds.get(5));
			cs.setString(7,runIds.get(6));
			cs.registerOutParameter(8,oracle.jdbc.OracleTypes.VARCHAR);
			cs.execute();
			returnStr = cs.getString(8);
			flagStr.add(returnStr);
			cs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(cs != null){
				cs.close();
			}
			if(conn != null){
				conn.close();
			}
		}
		return flagStr;
	}
	@Override
	public List getAnyAccount(String mb) {
		List list=new ArrayList();
		try {
			String sql="select mb.*, op.o_id, op.o_name from MULTI_BANK_INFO mb left join bs_operators op on mb.sin_id = op.o_card where op.o_id = '"+mb+"' union all select mb.*, bm.man_id, bm.m_name from MULTI_BANK_INFO mb left join bs_man bm on mb.sin_id = bm.Card_No where bm.man_id = '"+mb+"' union all select mb.*, bc.clients_id, bc.clients_name from MULTI_BANK_INFO mb left join bs_clients bc on mb.sin_id = bc.card_no where bc.clients_id = '"+mb+"'";
			list=this.jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	@Override
	public List getAllMoney(String mb) {
		List list=new ArrayList();
		try {
			String sql="select (PK_CW_SAVE.up_getUserHKMoney('"+mb+"')) HKJE_MONEY from dual";
			list=this.jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	@Override
	public List getzhanghaobyoid(String mb) {
		List list=new ArrayList();
		try {
			String sql="select m.*,b.o_id MAN_BUSINESS from MULTI_BANK_INFO m  left join bs_operators b on m.sin_id = b.o_card where b.o_id='"+mb+"'";
			list=this.jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	@Override
	public List getNextYinci(String xtId, String manID, String contractTYPE,
			String yinci) {
		List list=new ArrayList();
		try {
			String sql="select max(yinci) yinci from ( select pe.yinci from cw_royalty cw left join yz_sprk yr on cw.xt_id=yr.sprk_id left join yz_print_explain pe on yr.pe_id=pe.pe_id where cw.jiafang = '"+manID+"' and cw.is_destroy='0' and yr.xt_id= (select sk.xt_id from yz_sprk sk where sk.sprk_id = '"+xtId+"'))";
			list=this.jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	@Override
	public List getNextYinciVerify(String xtId, String manID) {
		List list=new ArrayList();
		try {
			String sql="select is_verify is_verify from (select pe.yinci, cw.is_verify from cw_royalty cw left join yz_sprk yr on cw.xt_id = yr.sprk_id left join yz_print_explain pe on yr.pe_id = pe.pe_id where cw.jiafang = '"+manID+"' and cw.is_destroy = '0' and yr.xt_id = (select sk.xt_id from yz_sprk sk where sk.sprk_id = '"+xtId+"') and pe.yinci =(select max(yinci) from (select pe.yinci, cw.is_verify from cw_royalty cw left join yz_sprk yr on cw.xt_id = yr.sprk_id left join yz_print_explain pe on yr.pe_id = pe.pe_id where cw.jiafang = '"+manID+"' and cw.is_destroy = '0' and yr.xt_id = (select sk.xt_id from yz_sprk sk where sk.sprk_id = '"+xtId+"'))))";
			list=this.jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	@Override
	public List getIsOrSave(String xtId,String manID,String yinci,int number) {
		List list=new ArrayList();
		try {
			String sql="SELECT sum(cb.bsmoney) BSMONEY FROM CW_RELECANCETABLE CB WHERE CB.XT_ID='"+xtId+"' AND CB.JIAFANG='"+manID+"' AND CB.YINCI='"+yinci+"' and rownum=1 order by cb.input_date asc ";
			list=this.jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();  
		}
		return list;
	}
	
	@Override
	public List getNowOidMan(String oid) {
		List list=new ArrayList();
		try {
			String sql=" select count(*) count  from sys_role_user where role_id = '1' and user_id = '"+oid+"'";
			list=this.jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	@Override
	public int getBookDelYinci(String xtId,String manID,String yinci,int number) {
		int count=0;
		try {
			String sql="delete from CW_RELECANCETABLE ce where ce.xt_id='"+xtId+"' and ce.jiafang='"+manID+"' and ce.yinci='"+yinci+"'";
			count=this.jdbcTemplate.update(sql);
			System.out.println(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	
	@Override
	public List getXtInfo(String xtId,String yinci) {
		List list=new ArrayList();
		try {
			String sql="select bi.total_amount from VV_BOOKYINCI bi where bi.pe_id='"+xtId+"'";
			list=this.jdbcTemplate.queryForList(sql);
			System.out.println(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	@Override
	public List getPubMoney(String XT_PUB_ID) {
		List list=new ArrayList();
		try {
			String sql="select be.first_money,be.campu_money from bs_publisher be  where be.pub_id='"+XT_PUB_ID+"'";
			list=this.jdbcTemplate.queryForList(sql);
			System.out.println(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	@Override
	public List getTrueContra(String xtId, String manID) {
		List list=new ArrayList();
		try {
			String sql="select count(*) have from bj_contract bc where bc.jiafang='"+manID+"' and bc.xt_id=(select sk.xt_id from yz_sprk sk where sk.sprk_id = '"+xtId+"')";
			list=this.jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}



    @Override
    public List<String> getzzJTBM(List<String> runIds) throws Exception {
        Connection conn = null;
        CallableStatement cs = null;
        String returnStr = "";
        List<String> flagStr = new ArrayList<String>();
        try {
            conn =  this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{Call PK_CW_SAVE1.UF_ZZ_AMOUNTS(?,?,?,?,?,?,?)}";
            cs = conn.prepareCall(sql);
            cs.setString(1,runIds.get(0));
            cs.setString(2,runIds.get(1));
            cs.setString(3,runIds.get(2));
            cs.setString(4,runIds.get(3));
            cs.setString(5,runIds.get(4));
            cs.setString(6,runIds.get(5));
            cs.registerOutParameter(7,oracle.jdbc.OracleTypes.VARCHAR);
            cs.execute();
            returnStr = cs.getString(7);
            flagStr.add(returnStr);
            cs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(cs != null){
                cs.close();
            }
            if(conn != null){
                conn.close();
            }
        }
        return flagStr;
    }

    @Override
    public List<String> getzzJTBMTWO(List<String> runIds) throws Exception {
        Connection conn = null;
        CallableStatement cs = null;
        String returnStr = "";
        List<String> flagStr = new ArrayList<String>();
        try {
            conn =  this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{Call PK_CW_SAVE1.UF_ZZ_AMOUNTSTWO(?,?,?,?,?,?,?)}";
            cs = conn.prepareCall(sql);
            cs.setString(1,runIds.get(0));
            cs.setString(2,runIds.get(1));
            cs.setString(3,runIds.get(2));
            cs.setString(4,runIds.get(3));
            cs.setString(5,runIds.get(4));
            cs.setString(6,runIds.get(5));
            cs.registerOutParameter(7,oracle.jdbc.OracleTypes.VARCHAR);
            cs.execute();
            returnStr = cs.getString(7);
            flagStr.add(returnStr);
            cs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(cs != null){
                cs.close();
            }
            if(conn != null){
                conn.close();
            }
        }
        return flagStr;
    }

    public double getMarketAmount(String xt_id){
        double money =0;
        try {
            String sql="select sum(b.prepaid_balance) from vv_bx_yxysedb  b  where b.is_verify='1' and b.is_destroy='0'  and  b.xt_id = '"+xt_id+"'";
            money=this.jdbcTemplate.queryForObject(sql,Double.class);
            System.out.println(sql);
        }catch(Exception e){
            e.printStackTrace();
        }finally {

        }

        return money;
    }

    @Override
    public List get_zzdata(String CWZZ_ID, String PAY_PARTY, String BASIS_ACCOUNT, String TOPIC_SELECTED) {
        List list=new ArrayList();
        try {
            String sql="select  * from  vv_dz_data z where z.pay_party='"+PAY_PARTY+"'and Z.BASIS_ACCOUNT='"+BASIS_ACCOUNT+"'and Z.topic_selected='"+TOPIC_SELECTED+"'";
            if(StringUtils.isNotBlank(CWZZ_ID)) {
				sql += " and z.CWZZ_ID != '" + CWZZ_ID + "'";
			}
            list=this.jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

	@Override
	public boolean checkEmptyMonthZZData(String XT_NAME, String PAY_PARTY, String ZZDZ_MONTH) {
		List list=new ArrayList();
		try {
			String sql="SELECT 1 FROM zc_authorcheck_item CMI LEFT JOIN zc_authorcheck CM ON CMI.CWZZ_ID = CM.CWZZ_ID "
					+ " WHERE cm.is_destroy = '0' AND ? = to_char(to_date(cmi.ZZDZ_MONTH, 'yyyy-MM'), 'yyyy-MM') AND ? = CMI.TOPIC_SELECTED AND ? = cm.pay_party";
			list=this.jdbcTemplate.queryForList(sql, ZZDZ_MONTH, XT_NAME, PAY_PARTY);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return CollectionUtils.isEmpty(list);
	}
}
